# pip install openpyxl==3.1.2
from openpyxl import Workbook, load_workbook
from tkinter import filedialog


class txt_tools(object):
    def __init__(self,txt_file):
         self.f2=open(txt_file, 'a', encoding='UTF-8')

    def MakeFolder1(self):
        self.f2.write('<Style id="cqt">'+'\n')
        self.f2.write('<IconStyle>'+'\n')
        self.f2.write('<color>e5ff0000</color><scale>0.8</scale>'+'\n')
        self.f2.write('<Icon><href>http://maps.google.com/mapfiles/kml/shapes/shaded_dot.png</href></Icon>'+'\n')
        self.f2.write('</IconStyle>'+'\n')
        self.f2.write('<LabelStyle><color>e580ffff</color></LabelStyle>'+'\n')
        self.f2.write('</Style>'+'\n')
        self.f2.write('\n')

    def MakeFolder2(self):
        self.f2.write('<Style id="25516776960">'+'\n')
        self.f2.write('<PolyStyle><color>FF00FFFF</color></PolyStyle>'+'\n')
        self.f2.write('<LineStyle><color>FF00FFFF</color><width>3</width></LineStyle>'+'\n')
        self.f2.write('</Style>'+'\n')
        self.f2.write('\n')

    def MakeFolder3(self):
        self.f2.write('<Style id="mycat">'+'\n')
        self.f2.write('<IconStyle>')
        self.f2.write('<color>FF00FFFF</color><scale>1.5</scale>'+'\n')
        self.f2.write('<Icon><href>http://maps.google.com/mapfiles/kml/pushpin/ylw-pushpin.png</href></Icon>'+'\n')
        self.f2.write('</IconStyle>'+'\n')
        self.f2.write('<LabelStyle><color>e580ffff</color></LabelStyle>'+'\n')
        self.f2.write('</Style>'+'\n')
        self.f2.write('\n')

    def MakePlacemark1(self,k,v):
        self.f2.write('<Placemark>'+'\n')
        self.f2.write('<name>' + k + '</name>'+'\n')
        #self.f2.write ('<name>' + Str(ThisWorkbook.Sheets("拉线图制作").Cells(Place1, 9)) + '米' + '</name>'+'\n')
        self.f2.write('<styleUrl>#cqt</styleUrl>'+'\n')
        self.f2.write('<Point>'+'\n')

        c = v + ",0 "
        self.f2.write('<coordinates>' + c + '</coordinates>'+'\n')

        self.f2.write('</Point>'+'\n')
        self.f2.write('\n')
        c=''

    def MakePlacemark2(self,site_name,jwd):
        self.f2.write('<Placemark>'+'\n')
        self.f2.write('<name><![CDATA[' + site_name + ']]></name>'+'\n')
        self.f2.write('<styleUrl>#mycat</styleUrl>'+'\n')
        self.f2.write('<Point>'+'\n')

        c = jwd + ",0 "
        self.f2.write('<coordinates>' + c + '</coordinates>'+'\n')

        self.f2.write('</Point>'+'\n')
        self.f2.write('\n')
        c = ""

    def MakeDescription1(self,k):
        self.f2.write('<description>'+'\n')
        self.f2.write('<![CDATA['+'\n')
        self.f2.write('<table width="400" border="2">'+'\n')
        self.f2.write('<tr>'+'\n')
        self.f2.write('<td width="2%"><p align="middle">'+'\n')
        self.f2.write('<name>' + '测试点名' + '</name>'+'\n')
        self.f2.write('</td>'+'\n')
        self.f2.write('<td width="6%"><p align="middle">'+'\n')

        self.f2.write('<name>' + k + '</name>'+'\n')

        self.f2.write('</tr>'+'\n')
        self.f2.write('</table>'+'\n')
        self.f2.write('<p>'+'\n')
        self.f2.write('<a href="mailto:XXXX?subject=关于规划点与测试点图层的问题">联系作者(XXX)</a>'+'\n')
        self.f2.write('</p>'+'\n')
        self.f2.write(']]>'+'\n')
        self.f2.write('</description>'+'\n')
        self.f2.write('</Placemark>'+'\n')
        self.f2.write('\n')

    def MakeDescription2(self,site_name,jwd,fwj):
        jd=jwd.split(",")[0]
        wd=jwd.split(",")[1]
        self.f2.write ('<description>'+'\n')
        self.f2.write ('<![CDATA['+'\n')
        self.f2.write ('<table width="400" border="2">'+'\n')
        self.f2.write ('<tr>'+'\n')
        self.f2.write ('<td width="2%"><p align="middle">'+'\n')
        self.f2.write ('<name>' + '站名' + '</name>'+'\n')
        self.f2.write ('</td>'+'\n')
        self.f2.write ('<td width="6%"><p align="middle">'+'\n')
        self.f2.write ('<name>' + site_name + '</name>'+'\n')
        self.f2.write ('</tr>'+'\n')
        self.f2.write ('<tr>'+'\n')
        self.f2.write ('<td width="2%"><p align="middle">'+'\n')
        self.f2.write ('<name>' + '方位角' + '</name>'+'\n')
        self.f2.write ('</td>'+'\n')
        self.f2.write ('<td width="6%"><p align="middle">'+'\n')
        self.f2.write ('<name>' + str(fwj) + '</name>'+'\n')
        self.f2.write ("</tr>"+'\n')
        self.f2.write ('<tr>'+'\n')
        self.f2.write ('<td width="2%"><p align="middle">'+'\n')
        self.f2.write ('<name>' + '经度' + '</name>'+'\n')
        self.f2.write ('</td>'+'\n')
        self.f2.write ('<td width="6%"><p align="middle">'+'\n')
        self.f2.write ('<name>' + jd + '</name>'+'\n')
        self.f2.write ('</tr>'+'\n')
        self.f2.write ('<tr>'+'\n')
        self.f2.write ('<td width="2%"><p align="middle">'+'\n')
        self.f2.write ('<name>' + '纬度' + '</name>'+'\n')
        self.f2.write ('</td>'+'\n')
        self.f2.write ('<td width="6%"><p align="middle">'+'\n')
        self.f2.write ('<name>' + wd + '</name>'+'\n')
        self.f2.write ('</tr>'+'\n')
        self.f2.write ('</table>'+'\n')
        self.f2.write ('<p>'+'\n')
        self.f2.write ('<a href="mailto:XXXX?subject=关于规划点与测试点图层的问题">联系作者(XXX)</a>'+'\n')
        self.f2.write ('</p>'+'\n')
        self.f2.write (']]>'+'\n')
        self.f2.write ('</description>'+'\n')
        self.f2.write ('</Placemark>'+'\n')
        self.f2.write ('\n')

    def MakeKML2(self,all_values):
        self.f2.write('<?xml version="1.0" encoding="utf-8"?>'+'\n')
        self.f2.write('<kml xmlns="http://www.opengis.net/kml/2.2" xmlns:kml="http://www.opengis.net/kml/2.2" xmlns:atom="http://www.w3.org/2005/Atom">'+'\n')
        ##============================================写测试点========================================
        self.f2.write('<Document>'+'\n')
        self.f2.write('<name>规划站点与投诉</name>'+'\n')
        self.f2.write('<Folder>'+'\n')
        self.f2.write('<name>测试点</name>'+'\n')
        self.MakeFolder1()

        for guihau_site_dics in all_values:
            for guihau_site_name, guihua_values in guihau_site_dics.items():
                for tousu_dic in guihua_values[1]:
                    for k, v in tousu_dic.items():
                        self.MakePlacemark1(k,v)
                        self.MakeDescription1(k)

        self.f2.write('</Folder>'+'\n')
        ##==============================================写规划点========================================
        self.f2.write('<Folder>'+'\n')
        self.f2.write('<name>规划点</name>'+'\n')
        self.MakeFolder3()

        for guihau_site_dics in all_values:
            for guihau_site_name, guihua_values in guihau_site_dics.items():
                cd = guihua_values[0][0]  # 规划经纬度  ['121.338118,28.583169', None]

                self.MakePlacemark2(guihau_site_name,guihua_values[0][0])
                self.MakeDescription2(guihau_site_name,guihua_values[0][0],guihua_values[0][1])
        self.f2.write('</Folder>'+'\n')
        ##=============================================写拉线==============================================
        self.f2.write('<Folder>'+'\n')
        self.f2.write('<name>拉线</name>'+'\n')
        self.MakeFolder2()

        for guihau_site_dics in all_values:
            for guihau_site_name, guihua_values in guihau_site_dics.items():
                cd = guihua_values[0][0]  # 规划经纬度  c,d

                self.f2.write('<Placemark>'+'\n')
                self.f2.write('<name>'+'\n')
                self.f2.write('<![CDATA[' + guihau_site_name + ']]>'+'\n')
                self.f2.write('</name>'+'\n')
                self.f2.write('<styleUrl>#25516776960</styleUrl>'+'\n')
                self.f2.write('<LineString>'+'\n')
                self.f2.write('<coordinates>'+'\n')

                for tousu_dic in guihua_values[1]:
                    for k, ab in tousu_dic.items():
                        e = ab + ",0 " + cd + '\n'
                        self.f2.write(e)

                self.f2.write('</coordinates>'+'\n')
                self.f2.write('</LineString>'+'\n')
                self.f2.write('</Placemark>'+'\n')

        self.f2.write('</Folder>'+'\n')
        self.f2.write('</Document>'+'\n')
        self.f2.write('</kml>'+'\n')

        self.f2.close()
        print("生成完成！")


class excel_tools:
    def __init__(self):
         self.excel_file= filedialog.askopenfilename(title='导入EXCEL', filetypes=[('Excel', '*.xlsx')])

    @property
    def open_excel(self):
        workbook = load_workbook(self.excel_file)
        return workbook

    def read_all(self, sheet_name):

        wb = self.open_excel
        ws = wb[sheet_name]
        myRows = list(ws.values)[1:]

        all_values = []
        global guihua_tousu_values
        global tousu_dict
        global tousu_values
        global guihua_dics
        global tousu_count
        global site_key
        n=0
        # for row in ws.iter_rows(min_row=2, max_row=ws.max_row, min_col=1,max_col=8):
        for myrow in myRows:
            if not myrow[0] is None:
                guihua_tousu_values = []   # 规划，投诉，数据规划成一个列表，作为规划站名（key），对应的值（value）
                guihua_values = []  # 规划站点的经纬度，方位角，规划成一个列表
                tousu_dict={}      # 每一个投诉规划一个字典，投诉名+经纬度
                tousu_values = []    # 所有投诉，规划一个投诉列表
                guihua_dics = {}         # 一个规划站点+n个投诉是一个字典，先置空
                tousu_count = myrow[4]     # 投诉数量
                site_key = myrow[0]            # 规划站名

                guihua_jwd_values=str(myrow[1]) + ',' + str(myrow[2])
                guihua_values.append(guihua_jwd_values)
                guihua_values.append(myrow[3])

                tousu_jwd_values = str(myrow[6]) + ',' + str(myrow[7])
                tousu_dict[myrow[5]] = tousu_jwd_values
                tousu_values.append(tousu_dict)

                guihua_tousu_values.append(guihua_values)
            else:
                tousu_dict = {}
                n = n + 1
                tousu_jwd_values=str(myrow[6]) + ',' + str(myrow[7])
                tousu_dict[myrow[5]] = tousu_jwd_values
                tousu_values.append(tousu_dict)
                if n+1 == tousu_count:
                    n=0
                    guihua_tousu_values.append(tousu_values)
                    guihua_dics[site_key] = guihua_tousu_values
                    all_values.append(guihua_dics)

        return all_values

if __name__ == '__main__':
    et = excel_tools()
    all_values=et.read_all('数据模板')

    tt = txt_tools(txt_file="拉线图.kml")
    tt.MakeKML2(all_values)









